package com.adfframeworks.labs.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.adfframeworks.labs.dao.CommunityMembersDAO;
import com.adfframeworks.labs.domain.Community;
import com.adfframeworks.labs.domain.Employee;

@Repository
public class CommunityMembersDAOImpl extends JdbcDaoSupport implements
		CommunityMembersDAO {

	@Autowired
	CommunityMembersDAOImpl(DataSource dataSource) {
		setDataSource(dataSource);
	}

	@Override
	public void create(Integer community, Integer employee) {
		String SQL = "INSERT INTO Community_Members (Community_ID, Member_ID) VALUES (?, ?)";
		getJdbcTemplate().update(SQL, new Object[] { community, employee });
		System.out.println("CommunityMembersDAOImpl.create(" + community + ", "
				+ employee + ")");
	}

	@Override
	public List<Community> listCommunitiesOfEmployee(Integer employee) {
		String SQL = "SELECT * FROM Community_Members, Community "
				+ "WHERE Community_Members.Community_ID = Community.Community_ID "
				+ "AND Member_ID = ?";
		List<Community> communities = getJdbcTemplate().query(SQL,
				new Object[] { employee }, new CommunityMapper());
		return communities;
	}

	@Override
	public List<Community> listCommunitiesAsOwner(Integer employee) {
		String SQL = "SELECT * FROM Community "
				+ "WHERE Owner = ?";
		List<Community> communities = getJdbcTemplate().query(SQL,
				new Object[] { employee }, new CommunityMapper());
		return communities;
	}

	@Override
	public List<Community> listCommunitiesAsNotOwner(Integer employee) {
		String SQL = "SELECT * FROM Community_Members, Community "
				+ "WHERE Community_Members.Community_ID = Community.Community_ID "
				+ "AND Member_ID = ? AND Owner <> ?";
		List<Community> communities = getJdbcTemplate().query(SQL,
				new Object[] { employee, employee }, new CommunityMapper());
		return communities;
	}

	@Override
	public List<Employee> listEmployeesFromCommunity(Integer community) {
		String SQL = "SELECT * FROM Community_Members, Employee "
				+ "WHERE Member_ID = Employee_ID "
				+ "AND Member_ID = ?";
		List<Employee> employees = getJdbcTemplate().query(SQL,
				new Object[] { community }, new EmployeeMapper());
		return employees;
	}

	@Override
	public void delete(Integer community, Integer employee) {
		String SQL = "DELETE FROM Community_Members WHERE Community_ID = ? AND Member_ID = ?";
		getJdbcTemplate().update(SQL, new Object[] { community, employee });
		System.out.println("CommunityMembersDAOImpl.delete(" + community + ", " + employee + ")");
	}

	@Override
	public void deleteCommunity(Integer community) {
		String SQL = "DELETE FROM Community_Members WHERE Community_ID = ?";
		getJdbcTemplate().update(SQL, new Object[] { community });
		System.out.println("CommunityMembersDAOImpl.deldeleteCommunityete(" + community + ")");
	}

	@Override
	public void deleteEmployee(Integer employee) {
		String SQL = "DELETE FROM Community_Members WHERE Member_ID = ?";
		getJdbcTemplate().update(SQL, new Object[] { employee });
		System.out.println("CommunityMembersDAOImpl.deleteEmployee(" + employee + ")");
	}

}
